package com.alog.yzd.jdbc;

import com.alog.yzd.utils.Print;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class JdbcUtils {
    private final String MYSQL_USERNAME = "root";
    private final String MYSQL_PASSWORD = "123456";
    private final String MYSQL_DRIVER = "com.mysql.cj.jdbc.Driver";
//    private final String MYSQL_DRIVER = "com.mysql.jdbc.Driver";
    private final String MYSQL_URL= "jdbc:mysql://localhost:3306/mydb";


    private Connection mConn ;
    private PreparedStatement mPreStatement;
    private ResultSet mResultSet;


    public JdbcUtils(){
        try{
            Class.forName(MYSQL_DRIVER);
//            DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
//            System.setProperty("jdbc.drivers",MYSQL_DRIVER);
            Print.print("注册驱动成功!!!2");
        }catch(Exception e){
            Print.print("注册驱动失败!!!");
            e.printStackTrace();
        }
    }

    public Connection getConn(){
        try{
            mConn = DriverManager.getConnection(MYSQL_URL, MYSQL_USERNAME, MYSQL_PASSWORD);
        }catch(Exception e){
            e.printStackTrace();
            Print.print("获取连接失败");
        }
        return mConn;
    }

    /**
     * 插入 / 修改数据
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public boolean updateByPreparedStatement(String sql, List<Object> params) throws SQLException {
        boolean flag = false ;
        int result = -1;
        int index = 1;
        mPreStatement = mConn.prepareStatement(sql);
        if(params != null && !params.isEmpty()){
            for(Object obj : params){
                mPreStatement.setObject(index++, obj);
            }
        }
        result = mPreStatement.executeUpdate();
        flag = result > 0 ? true : false;
        return flag;
    }

    /**
     * 查询单条数据
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public Map<String , Object> findSimpleResult(String sql, List<Object> params) throws SQLException {
        mPreStatement = mConn.prepareStatement(sql);
        Map<String, Object> map = new HashMap<>();
        int index = 1;
        if(params != null && !params.isEmpty() ){
            for(Object obj : params){
                mPreStatement.setObject(index++, obj);
            }
        }

        mResultSet = mPreStatement.executeQuery();
        //获取结果的元信息
        ResultSetMetaData meteData = mResultSet.getMetaData();
        //获取列数
        int colsLen = meteData.getColumnCount();


        while (mResultSet.next()){
            for(int i = 0 ; i < colsLen; i++){
                //获取列名
                String colName = meteData.getColumnName(i+1);//从1开始
                //获取列值
                //Object colValue = mResultSet.getObject(colName);
                Object colValue = mResultSet.getObject(i + 1); //从1开始
                if(colValue == null){
                    colValue = "";
                }
                map.put(colName, colValue);
            }
        }
        return map;

    }

    /**
     * 查询多条数据
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public List<Map<String, Object>> findMoreResult(String sql, List<Object> params) throws SQLException {
        List<Map<String, Object>> list = new ArrayList<>();
        mPreStatement = mConn.prepareStatement(sql);
        int index = 1 ;
        if(params != null && !params.isEmpty()){
            for(Object obj : params){
                mPreStatement.setObject(index++, obj);
            }
        }

        mResultSet = mPreStatement.executeQuery();
        ResultSetMetaData metaData = mResultSet.getMetaData();
        int colsLen = metaData.getColumnCount();
        while(mResultSet.next()){
            Map<String, Object> map = new HashMap<>();
            for(int i = 0 ; i < colsLen; i++){
                String colName = metaData.getColumnName(i + 1);
                Object colValue = mResultSet.getObject(colName);
                if(colValue == null){
                    colValue = "";
                }
                map.put(colName, colValue);
            }
            list.add(map);
        }

        return list;
    }

    /**
     * 查询单个JavaBean
     * @param sql
     * @param params
     * @param cls
     * @param <T>
     * @return
     * @throws Exception
     */
    public <T> T findSimpleRefResult(String sql, List<Object> params , Class<T> cls) throws Exception {
        T resultObj = null;
        int index = 1 ;
        mPreStatement = mConn.prepareStatement(sql);
        if(params != null && !params.isEmpty()){
            for(Object obj : params){
                mPreStatement.setObject(index++, obj);
            }
        }

        mResultSet = mPreStatement.executeQuery();
        ResultSetMetaData metaData = mResultSet.getMetaData();
        int colsLen = metaData.getColumnCount();
        while (mResultSet.next()){
            resultObj = cls.newInstance();
            for(int i = 0; i < colsLen; i++){
                String colName = metaData.getColumnName(i + 1);
                Object colValue = mResultSet.getObject(i + 1);
                if(colValue == null){
                    colValue = "";
                }
                Field field = cls.getDeclaredField(colName);
                if(field != null){
                    field.setAccessible(true);
                    field.set(resultObj, colValue);
                }
            }

        }
        return resultObj;
    }

    /**
     * 查询多个JavaBean
     * @param sql
     * @param params
     * @param cls
     * @param <T>
     * @return
     * @throws Exception
     */
    public <T> List<T> findMoreRefResult(String sql , List<Object> params, Class<T> cls) throws Exception {
        List<T> list = new ArrayList<>();
        int index = 1 ;
        mPreStatement = mConn.prepareStatement(sql);
        if(params != null && !params.isEmpty()){
            for(Object obj : params){
                mPreStatement.setObject(index++, obj);
            }
        }

        mResultSet = mPreStatement.executeQuery();
        ResultSetMetaData metaData = mResultSet.getMetaData();
        int colsLen = metaData.getColumnCount();
        while(mResultSet.next()){
            T resultObj = cls.newInstance();
            for(int i = 0; i < colsLen; i++){
                String colName = metaData.getColumnName(i + 1);
                Object colValue = mResultSet.getObject(i+ 1);
                if(colValue == null){
                    colValue = "";
                }

                Field field = cls.getDeclaredField(colName);
                field.setAccessible(true);
                field.set(resultObj, colValue);
            }
            list.add(resultObj);
        }
        return list;
    }

    /**
     * 释放资源
     * @throws SQLException
     */
    public void releaseConn() throws SQLException {
        if(mResultSet != null){
            mResultSet.close();
        }

        if(mPreStatement != null){
            mPreStatement.close();
        }

        if(mConn != null){
            mConn.close();
        }
    }

}
